package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.Shop;
import sy.service.ShopServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-07-06.
 */
@Service
public class ShopServiceImpl implements ShopServiceI {

    @Autowired
    private JdbcTemplate template;

    @Override
    public DataGrid findShopDataGird(Shop s) {
        DataGrid dg = new DataGrid();
        String sql = "select count(1) from p_goods p, p_info pi,s_code sc where pi.id=p.deptid and sc.code=p.shop_type AND sc.types='商品系列' ";
        sql = addWhere(s, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "select * from p_goods p, p_info pi,s_code sc where pi.id=p.deptid and sc.code=p.shop_type AND sc.types='商品系列' ";
        sql = addWhere(s, sql);
        sql = addOrderBy(s, sql);
        sql = addLimit(s, sql);
        final List<Shop> shopList = new ArrayList<Shop>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Shop s=new Shop();
                s.setId(rs.getString("p.id"));
                s.setDeptId(rs.getString("pi.id"));
                s.setDeptName(rs.getString("pi.name"));
                s.setName(rs.getString("p.names"));
                s.setPrice(rs.getString("p.price1"));
                s.setJdPrice(rs.getString("p.price2"));
                s.setWhPrice(rs.getString("p.price3"));
                s.setCjPrice(rs.getString("p.price4"));
                s.setComment(rs.getString("p.descs"));
                s.setPic(rs.getString("p.pic"));
                s.setTypeId(rs.getString("sc.code"));
                s.setTypeName(rs.getString("sc.descs"));
                s.setJbDescribe(rs.getString("p.jb_describe"));
                s.setWhDescribe(rs.getString("p.wh_describe"));
                s.setCjDescribe(rs.getString("p.cj_describe"));
                s.setOrder(rs.getString("p.orderid"));
                s.setYear(rs.getString("p.year"));
                shopList.add(s);
            }});
        dg.setRows(shopList);
        return dg;
    }

    @Override
    public int addShopService(Shop s) throws Exception {
        String sql="INSERT INTO p_goods(id, names, price1, price2, price3, price4, deptid, pic, descs, jb_describe, wh_describe, cj_describe, shop_type, orderid, year) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int rows=template.update(sql,new Object[]{s.getId(),s.getName(),findPrice(s.getPrice()),findPrice(s.getJdPrice()),findPrice(s.getWhPrice()),findPrice(s.getCjPrice()),s.getDeptId(),s.getPic(),s.getComment(),s.getJbDescribe(),s.getWhDescribe(),s.getCjDescribe(),s.getTypeId(),s.getOrder(),s.getYear()});
        return rows;
    }

    @Override
    public int editShopService(Shop s) throws Exception {
        System.out.println(findPrice(s.getPrice()));
        String sql="UPDATE p_goods SET names=?, price1=?, price2=?, price3=?, price4=?, deptid=?, pic=?, descs=?, jb_describe=?, wh_describe=?, cj_describe=?, shop_type=?, orderid=?, year=? WHERE id=?";
        int rows=template.update(sql,new Object[]{s.getName(),findPrice(s.getPrice()),findPrice(s.getJdPrice()),findPrice(s.getWhPrice()),findPrice(s.getCjPrice()),s.getDeptId(),s.getPic(),s.getComment(),s.getJbDescribe(),s.getWhDescribe(),s.getCjDescribe(),s.getTypeId(),s.getOrder(),s.getYear(),s.getId()});
        return rows;
    }


    public String findPrice(String price){
        String str[]=price.split("\\.");
        if (str.length==1) price=price+".00";
        return price;
    }
    @Override
    public int deleteShopService(String ids) throws Exception {
        String sql="DELETE FROM p_goods where id=?";
        int rows=0;
        String ids1[]=ids.split(",");
        for (int i = 0; i <ids1.length ; i++) {
            rows=template.update(sql,new Object[]{ids1[i]});
        }
        return rows;
    }

    @Override
    public List<BaseEntity> findTypeList(String types) {
        String sql="SELECT *from s_code WHERE types=?";
        final List<BaseEntity> entityList=new ArrayList<>();
        template.query(sql, new Object[]{types}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString("code"));
                entity.setText(rs.getString("descs"));
                entityList.add(entity);
            }
        });
        return entityList;
    }

    private String addLimit(Shop s, String sql) {
        int fromRow = (s.getPage() - 1) * s.getRows();
        sql += " limit " + fromRow + "," + s.getRows();
        return sql;
    }

    private String addWhere(Shop s, String sql) {
        if(!StringUtil.isNullOrEmpty(s.getDeptId())) {
            sql += " and p.deptid = '" + s.getDeptId() + "'";
        }
        if(!StringUtil.isNullOrEmpty(s.getTypeId())) {
            sql += " and p.shop_type = '" + s.getTypeId() + "'";
        }
        if(!StringUtil.isNullOrEmpty(s.getName())) {
            sql += " and p.names like '%" + s.getName() + "%'";
        }

        return sql;
    }

    private String addOrderBy(Shop s, String sql) {
        if (s.getSort() != null) {
            if("deptName".equals(s.getSort())) s.setSort("p.deptid");
            if("typeName".equals(s.getSort())) s.setSort("p.shop_type");
            if("name".equals(s.getSort())) s.setSort("p.names");
            if("order".equals(s.getSort())) s.setSort("p.orderid");
            sql += " order by " + s.getSort() + " " + s.getOrder();
        }
        return sql;
    }
}
